' Class for Accessing the database
' This class has the functions to fetch data from database 
' or write data to the database.
' Programming: 
' Date: 15/June/2009
Option Explicit On

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataAccess
    '  
    'Private Shared SQLConn As SqlConnection
    'Private Shared _LastException As Exception
    'Private Shared _Trans As SqlTransaction
    'Private Shared _bInTransaction As Boolean = False

    Private Shared SQLConn As SqlConnection
    Private Shared _LastException As Exception
    Private Shared _Trans As SqlTransaction
    Private Shared _bInTransaction As Boolean = False
    Private Function OpenDB() As Boolean

        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader

        Try
            SQLConn = New SqlConnection
            'If Environment.UserDomainName.Trim = "GV" Then
            SQLConn.ConnectionString = CType(configurationAppSettings.GetValue("connectionString", GetType(System.String)), String)
            'SQLConn.ConnectionString = "Data Source=localhost;Initial Catalog=Global;Integrated Security=True"
            'If SQLConn.ConnectionString.Contains("gvdb.crmvisas.com") Then
            'SQLConn.ConnectionString = SQLConn.ConnectionString & ";Database=global;User Id=GlobalWeb;Password=0cSn3FKzr9;timeout=30;"
            'End If
            'Else
            'SQLConn.ConnectionString = CType(configurationAppSettings.GetValue("globalConnStringExt", GetType(System.String)), String)
            'End If
            SQLConn.Open()
            Return True
        Catch exc As System.Data.SqlClient.SqlException

            If Not IsNothing(_Trans) Then
                RollbackTrans()
            Else

            End If
            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            Return False
        Catch ex As Exception
            LastDBError = ex
            Return False
        End Try

    End Function

    Private Sub CloseDB()

        SQLConn.Close()
        SQLConn.Dispose()

    End Sub

    Public Function GetSQLDataTable(ByVal SQL As String) As DataTable
        If (String.IsNullOrEmpty(SQL)) Then
            Return Nothing
        End If
        Dim s As New DataAccess
        If Not s.OpenDB() Then
            MsgBox(s.LastDBError.Message, MsgBoxStyle.Critical)
        End If
        Dim SQLCmd As New SqlCommand(SQL, DataAccess.SQLConn)
        Dim SQLAdapt As New SqlDataAdapter
        Dim SQLData As New DataSet
        Try
            If IsNothing(DataAccess.SQLConn) Then
                Return Nothing
                Exit Function
            End If
            'If Not IsNothing(_Trans) Then
            '    SQLCmd.Transaction = _Trans
            'End If
            SQLAdapt.SelectCommand = SQLCmd
            SQLAdapt.SelectCommand.CommandText = SQL
            SQLAdapt.Fill(SQLData)
            If SQLData.Tables.Count > 0 Then
                Return SQLData.Tables(0)
            Else
                Return Nothing
            End If
        Catch exc As System.Data.SqlClient.SqlException

            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If
            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            Return Nothing
        Catch ex As Exception

            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If

            MsgBox(ex.Message, MsgBoxStyle.Critical)
            LastDBError = ex
            Return Nothing
        Finally
            s.CloseDB()
            SQLCmd.Dispose()
            SQLAdapt.Dispose()
            SQLData.Dispose()
        End Try

    End Function

    Public Function GetSQLDataSet(ByVal SQL As String) As DataSet

        Dim s As New DataAccess
        If Not s.OpenDB() Then

            MsgBox(s.LastDBError.Message, MsgBoxStyle.Critical)
        End If

        Dim SQLCmd As New SqlCommand(SQL, SQLConn)
        Dim SQLAdapt As New SqlDataAdapter
        Dim SQLData As New DataSet

        Try

            If IsNothing(DataAccess.SQLConn) Then
                Return Nothing
                Exit Function
            End If

            If Not IsNothing(_Trans) Then
                SQLCmd.Transaction = _Trans
            End If
            SQLAdapt.SelectCommand = SQLCmd
            SQLAdapt.Fill(SQLData)

            If SQLData.Tables.Count > 0 Then
                Return SQLData
            Else
                Return Nothing
            End If
        Catch exc As System.Data.SqlClient.SqlException

            If Not IsNothing(_Trans) Then
                RollbackTrans()
            End If
            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            Return Nothing
        Catch ex As Exception

            If Not IsNothing(_Trans) Then
                RollbackTrans()
            End If

            MsgBox(ex.Message, MsgBoxStyle.Critical)
            LastDBError = ex
            Return Nothing
        Finally
            s.CloseDB()
            SQLCmd.Dispose()
            SQLAdapt.Dispose()
            SQLData.Dispose()
        End Try

    End Function


    Public Function GetSQLDataReader(ByVal SQL As String) As SqlDataReader

        Dim s As New DataAccess
        If Not s.OpenDB() Then

            MsgBox(s.LastDBError.Message, MsgBoxStyle.Critical)
        End If

        Dim SQLCmd As New SqlCommand(SQL, SQLConn)
        Dim SQLReader As SqlDataReader

        Try

            If Not IsNothing(_Trans) Then
                SQLCmd.Transaction = _Trans
            End If
            SQLReader = SQLCmd.ExecuteReader
            Return SQLReader
        Catch exc As System.Data.SqlClient.SqlException

            If Not IsNothing(_Trans) Then
                RollbackTrans()
            End If
            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            Return Nothing
        Catch ex As Exception

            If Not IsNothing(_Trans) Then
                RollbackTrans()
            End If

            LastDBError = ex
            Return Nothing
        Finally
            s.CloseDB()
            SQLCmd.Dispose()
        End Try

    End Function

    Public Function GetSQLScalar(ByVal SQL As String) As Object

        Dim s As New DataAccess
        If Not s.OpenDB() Then
            If Not s.LastDBError Is Nothing Then
                MsgBox(s.LastDBError.Message, MsgBoxStyle.Critical)
                Return Nothing
            End If
        End If

        Dim SQLCmd As New SqlCommand(SQL, SQLConn)
        Dim Sclr As Object

        Try

            If Not IsNothing(_Trans) Then
                SQLCmd.Transaction = _Trans
            End If
            Sclr = SQLCmd.ExecuteScalar()
            Return Sclr
        Catch exc As System.Data.SqlClient.SqlException

            If Not IsNothing(_Trans) Then
                RollbackTrans()
            End If
            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            Return Nothing
        Catch ex As Exception

            If Not IsNothing(_Trans) Then
                RollbackTrans()
            End If

            Return Nothing
        Finally
            s.CloseDB()
            SQLCmd.Dispose()
            s = Nothing
        End Try

    End Function
    Public Function GetSQLScalar(ByVal SQL As String, ByVal blnS As Boolean) As Object

        Dim s As New DataAccess
        'If Not s.OpenDB() Then

        '    MsgBox(s.LastDBError.Message, MsgBoxStyle.Critical)
        'End If

        Dim SQLCmd As New SqlCommand(SQL, SQLConn)
        Dim Sclr As Object

        Try

            'If Not IsNothing(_Trans) Then
            '    SQLCmd.Transaction = _Trans
            'End If
            Sclr = SQLCmd.ExecuteScalar()
            Return Sclr
        Catch exc As System.Data.SqlClient.SqlException

            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If
            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            Return Nothing
        Catch ex As Exception

            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If

            Return Nothing
        Finally
            s.CloseDB()
            SQLCmd.Dispose()
            s = Nothing
        End Try

    End Function
    Public Property LastDBError() As System.Exception
        Get
            Return _LastException
        End Get
        Set(ByVal Ex As System.Exception)
            _LastException = Ex
        End Set
    End Property

    Public Function ExecuteSQL(ByVal SQL As String) As Boolean
        'Dim s As New DataAccess
        If Not Me.OpenDB() Then

            MsgBox(Me.LastDBError.Message, MsgBoxStyle.Critical)
        End If
        ' Dim tx As SqlTransaction = Nothing

        'tx = SQLConn.BeginTransaction
        Dim SQLCmd As New SqlCommand(SQL, SQLConn)
        Try
            'BeginTrans()
            'If Not IsNothing(_Trans) Then
            '    SQLCmd.Transaction = _Trans
            'End If

            SQLCmd.ExecuteNonQuery()
            'tx.Commit()
            ExecuteSQL = True

        Catch exc As System.Data.SqlClient.SqlException

            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If
            'If Not IsNothing(tx) Then
            '    tx.Rollback()
            'End If

            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            ExecuteSQL = False
        Catch ex As Exception
            ExecuteSQL = False

            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If

        Finally
            Me.CloseDB()
            SQLCmd.Dispose()
            's = Nothing
        End Try


    End Function
    Public Function ExecuteSQL(ByVal SQL As String, ByVal blnGetIdentity As Boolean) As Long
        Dim s As New DataAccess
        If Not s.OpenDB() Then

            MsgBox(s.LastDBError.Message, MsgBoxStyle.Critical)
        End If

        Dim SQLCmd As New SqlCommand(SQL, SQLConn)

        Try

            'If Not IsNothing(_Trans) Then
            '    SQLCmd.Transaction = _Trans
            'End If
            SQLCmd.ExecuteNonQuery()
            If blnGetIdentity = True Then
                Return GetIdentity()
            End If
            ExecuteSQL = 1
        Catch exc As System.Data.SqlClient.SqlException

            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If
            MsgBox("Please report the error to IT." & vbCrLf & vbCrLf & exc.Message, MsgBoxStyle.Critical, "IT")
            Return 0
        Catch ex As Exception
            Return 0
            'If Not IsNothing(_Trans) Then
            '    RollbackTrans()
            'End If

        Finally
            's.CloseDB()
            SQLCmd.Dispose()
            's = Nothing
        End Try


    End Function
    Public Function GetIdentity() As Long

        Return GetSQLScalar("select @@identity", True)
       
    End Function

    Public Sub BeginTrans()
        'Dim s As New DataAccess
        Try

            If Me.OpenDB() Then

                _Trans = SQLConn.BeginTransaction
                _bInTransaction = True
            End If

        Catch ex As Exception
            _bInTransaction = False

        Finally
            's = Nothing
        End Try
    End Sub

    Public Sub RollbackTrans()


        _Trans.Rollback()
        _Trans.Dispose()
        _Trans = Nothing
        _bInTransaction = False
    End Sub

    Public Function IsTransactionOpen() As Boolean
        If _Trans Is Nothing Then
            Return False
        Else
            Return True
        End If
    End Function

    Public Sub CommitTrans()

        _Trans.Commit()
        _Trans.Dispose()
        _Trans = Nothing

    End Sub
End Class
